Statistical Forecasting with Microsoft Excel Forecast Models

Current forecasting software can be problematic in its level of integration with Microsoft Excel forecasting models. Standalone forecasting software operates in a batch mode with Excel and is impossible to run interactively without custom programming. Excel forecasting add-ins are designed to work within Excel and can be automated to use with forecasting models, but lack support for damped trends, out-of-sample testing, and cumulative prediction intervals. Objective Insights has developed the Objective Insights Prediction Engine (OIPE), a general-purpose set of trending and statistics routines designed to be incorporated into Microsoft Excel forecast models. The OIPE features a comprehensive set of trending methods and in-sample trend accuracy statistics. In addition, the OIPE calculates point and cumulative prediction intervals for exponential smoothing models and uses an innovative approach for out-of-sample accuracy testing. Finally, the OIPE is designed to be incorporated into custom forecasting solutions in Microsoft Excel, improving forecast productivity and aiding insights into market conditions. The OIPE supports several varieties of trending methods, including moving average; linear, exponential, and polynomial regression; exponential smoothing; and Box-Jenkins (ARIMA). Exponential smoothing methods include simple, linear, and multiplicative trends, seasonality, and damping. Box-Jenkins includes both seasonal and non-seasonal methods. The OIPE provides a number of in-sample statistics for trends calculated in an Excel forecasting model; in-sample statistics are those calculated from the same dataset used to determine the fitted statistical model. The statistics included are measures of forecast accuracy or are used to compare the quality of fit among different statistical models. Another valuable capability in forecasting software is out-of-sample (holdout) testing, particularly when comparing the results of different forecasting methods. The OIPE implements out-of-sample testing with the additional enhancement of using rolling windows for the holdout samples. Rolling windows are essentially a sequential series of holdout samples and better take into account how variations in the data favor or disfavor particular trending methods compared to a single holdout sample. Prediction intervals are another useful feature of the OIPE and are calculated for the most commonly used exponential smoothing methods. Prediction intervals represent a range of possible outcomes expected to contain an unknown future value of product demand, for instance, with some specified probability (e.g., 95% or 80%). In addition to point prediction intervals, the OIPE also calculates cumulative (lead-time) prediction intervals. When forecasts for a number of time points are aggregated into a cumulative total (e.g., summing monthly or quarterly forecasts into a calendar year), one cannot simply use a weighted average of the individual prediction intervals, as there is covariance between those error terms. Failing to take the covariance into account will tend to underestimate the aggregate prediction interval. Objective Insights compared the forecast results of the OIPE against leading forecasting software using real-world customer data. Tests across eight such sets of data found that the OIPE performed as well or better than a leading software package. The OIPE is designed to be incorporated into Microsoft Excel forecasting models and was originally developed by Objective Insights to support our own forecasting models without having to resort to thirdparty software. The OIPE enables the forecasting model to easily support your forecasting process and Statistical Forecasting in Microsoft Excel Models Objective Insights Page 1 workflow, rather than having to work around the way in which third-party software imports data and exports results. Current State of Trend-Based Forecasting in Microsoft Excel Trend-based forecasting in Microsoft Excel currently ranges from simple (linear regression available in the Excel Analysis Toolpak) to sophisticated (Excel add-ins such as ForecastX and ezForecaster). In addition to interactive use, some of the add-ins can be programmed with Excel’s built-in Visual Basic for Applications, making them suitable for automatic use with forecasting models (e.g., updating forecast trends at the click of a button). Current add-ins suffer from several shortcomings, however. First, these add-ins appear to lack support for calculating damped trends when using exponential smoothing. This is important because it makes it more difficult for the forecaster to damp (roll off) trends with longer time horizons, where the expectation is that a trend is not likely to continue growing linearly over time but instead should start to moderate. Second, the add-ins do not have a provision for calculating forecast accuracy using out-of-sample testing. Out-of-sample testing withholds a portion of the dataset used for forecasting and uses the remaining data to determine the trend, which is then compared to the held-out sample to measure how well the trend predicted what really happened. The accuracy statistics provided by these add-ins are instead calculated in-sample (from the difference between the forecast model fitted to the entire dataset and the data itself), which is a less robust means of determining which forecast method yields the most accurate results. Current Excel add-ins also lack cumulative (lead-time) prediction intervals. Prediction intervals (level of certainty about the range into which a forecast may fall) are normally calculated at the level of the individual unit of time used in the forecast (i.e., a month for monthly forecasts). When aggregating monthly forecasts into a calendar year, however, one cannot simply use a weighted average of the monthly prediction intervals, as there is covariance between the monthly error terms. Failing to take the covariance into account will tend to underestimate the aggregate prediction interval. Standalone Forecasting Software Beyond Excel, there are several forecasting software packages that vary from specialized to general purpose. The Autobox software focuses on Box-Jenkins methods, but excludes other approaches to statistical forecasting. Forecast Pro is a sophisticated, full-featured forecasting package, but it shares the common flaw with all standalone forecasting software that it is difficult at best to integrate with Excel forecasting models in an automated fashion. For example, both Autobox and Forecast Pro have programming interfaces utilizing Windows dynamic link libraries (DLL’s), yet in practice, these interfaces cannot be controlled from Excel using Excel’s Visual Basic for Applications (VBA). Instead, customers wishing to integrate this software is an Excel model must write custom wrappers in computer languages like C++ in order to call the trending routines from Excel. Other alternatives to standalone forecasting software includes statistical software such as SAS and R, as well as trending functionality built into enterprise resource planning (ERP) systems. Statistical software is very comprehensive and flexible, offering a huge range of trending methods and accuracy statistics and the ability to add custom trending routines if needed. However, this comes at the cost of a steep learning curve and there is still the inability to integrate well with an Excel forecast model. Statistical Forecasting in Microsoft Excel Models Objective Insights Page 2 ERP software has the advantage of integration with corporate financial and manufacturing planning systems, but can be quite limited in terms of forecast method selection, customization of business rules, and fine-grained control over how individual products are trended. For example, the ability to calculate the model “backwards,” such as with Excel’s Goal Seek function, is missing. As with Excel add-ins, standalone trending software that we have reviewed lacks the capability to calculate cumulative prediction intervals. Objective Insights Prediction Engine The Objective Insights Prediction Engine (OIPE) is a general-purpose set of trending and statistics routines. The OIPE runs in Microsoft Excel 97 or later (including all Macintosh versions except for Excel 2008, which lacks Visual Basic) and is written in Visual Basic for Applications. As such, it needs no other supporting components or software, such as DLL’s. The OIPE was originally written in order to improve the forecast process with Excel models and circumvent other shortcomings of third-party software. By enabling closer integration of the trending software with the Excel forecast model, the OIPE facilitates a better forecasting workflow and faster turnaround of forecast results. The OIPE features a comprehensive set of trending methods and in-sample trend accuracy statistics. In addition, the OIPE calculates point and cumulative prediction intervals for exponential smoothing models and uses an innovative approach for out-of-sample accuracy testing. Finally, the OIPE is designed to be incorporated into custom forecasting solutions in Microsoft Excel, improving forecast productivity and aiding insights into market conditions. The OIPE is not a “black box,” so Objective Insights can review the calculation flow with clients to show what is being calculated and how. The formulas and calculation sequences can be laid bare to help explain the trending methods and the results. Trending Methods Trending a dataset with the OIPE assumes that the dataset has already been adjusted for extraneous factors that may distort the trend. For example, monthly prescription data is typically adjusted to account for number of days in the month and holiday effects; as an aside, Objective Insights has developed an innovative approach for such adjustments. As such, the trending methods below are intended to be applied to a single dataset at a time (i.e., there is no multivariate analysis). The OIPE supports the following well-accepted trending methods: • Moving average • Linear, exponential, and polynomial regression • Exponential smoothing • Box-Jenkins (ARIMA) Moving Average The OIPE calculates a simple trailing average of the last n data points; the projected trend is the value of the moving average at the last n data points in the historical data series. The period used for the moving average is easily changed in forecast models implementing this method. Statistical Forecasting in Microsoft Excel Models Objective Insights Page 3 Regression Types The linear regression routine in the OIPE uses the COBYLA (Constrained Optimization BY Linear Approximation, Powell, 1994) optimization algorithm to minimize the sum of squared errors. COBYLA is also used in the other regression routines, as well as in exponential smoothing and ARIMA, where minimization of the objective function is needed. Exponential regression applies a log transform to the data before using univariate linear regression, thereby allowing more accurate appraisal of data growing on a percentage basis. Polynomial regression adds quadratic and square root terms to univariate linear regression; the form of the regression equation is thus y = m1x + m2x2 + m3x1/2. Determination of the regression coefficients is conducted by minimizing the sum of squared errors using COBYLA as described above. The length of the historical data window used in these different types of regression analysis can be easily changed in the forecast model. Exponential Smoothing Exponential smoothing (ES) is a time series analysis method related to a moving average. Whereas all data points in a moving average are equally weighted, exponential smoothing applies a series of exponentially decreasing weights to the data points (hence the name). Exponential smoothing is a robust, relatively simple forecasting technique that is frequently one of the top-scoring methods in forecasting competitions, such as the M series of contests organized by Makridakis (1982, 1993, 1998). The degree to which recent data are more weighted than older data is determined by the smoothing parameters. These factors can be calculated for the level, slope (or trend), and seasonality of the dataset and are determined through an optimization process where an ES model is fitted to the historical dataset. Parameter values are selected to minimize the step-ahead squared errors between the ES model and the historical data and vary between zero and one, with values closer to one more heavily weighting recent data points. The selection of exponential smoothing method depends on the nature of the data. A time series that has little or no discernible trend is best addressed with simple (one-parameter) exponential smoothing, where only the smoothing factor for the level is calculated. Trended data are best addressed with two-parameter ES, where both the level and trend smoothing factors are calculated. This method is referred to as Holt exponential smoothing (after the originator of the technique) when the ES model uses a linear trend. Exponential (multiplicative) trends can also be used in ES models. Another extension of exponential smoothing is where the time series is seasonal, such as with allergy products or influenza vaccines. This variation (known as Holt-Winters or simply Winters when the trend is linear) adds a third smoothing parameter for the cyclical component. Seasonality can be either additive (where the amplitude is constant over time) or multiplicative (where the amplitude varies with the level of the time series). Trends may also incorporate a damping parameter, such that a trend’s slope will approach zero at some point in the future. The more damping applied, the faster the trend will level out. Damping is useful where the forecaster does not expect a trend to linearly continue in the long term, such as during a product’s initial growth phase. Damping can also be used to prevent the trend for a declining product from going below zero. Statistical Forecasting in Microsoft Excel Models Objective Insights Page 4 Another trending approach incorporated in the OIPE is the Theta method (Assimakopoulos & Nikolopoulos 2000), a forecasting model related to exponential smoothing. This method is useful for data where there is a slowly-evolving trend. The Theta method, also called simple exponential smoothing with drift, combines the level component found through simple exponential smoothing with a trend component computed as one-half the slope identified through linear regression on the entire time series. The OIPE implements the state-space approach to exponential smoothing as described in Hyndman et al (2008). The results of the state-space approach match those of the classical methods of exponential smoothing (e.g., Gardner 1985), such as Holt and Holt-Winters; however, the state-space approach provides the underlying statistical models generally lacking in the classical methods. As such, prediction intervals may then be calculated for many of the exponential smoothing types Exponential Smoothing Methods Implemented in OIPE Seasonality Trend Type None Additive Multiplicative